<?php


class Sherpa_ReportsController extends Zend_Controller_Action
{

    /**
     * Initializer
     */
	public function init()
	{
		Zend_Layout::getMvcInstance()->setLayout('layout');
	}

	/**
	 * Index Action
	 */
    public function indexAction()
    {}

    public function report1Action()
    {
		$this->_helper->viewRenderer->setNoRender();

        set_include_path(
        	WSB_PATH . 'sherpa/libraries/PEAR/' . PATH_SEPARATOR . get_include_path()
        );

        require_once('Spreadsheet/Excel/Writer.php');

        // Connect to database
        $db = Zend_Db_Table::getDefaultAdapter();

        // Load data
        $stmt = $db->query(
        	'SELECT		p.articleNumber,
        				DATE_FORMAT(o.orderDate, "%Y-%m") AS yearMonth,
        				SUM(pb.quantity) AS quantity,
        				ROUND(SUM(pb.price * pb.quantity), 2) AS total,
        				p.productName,
        				p.productLine
        	FROM		sherpa_orders AS o
        		INNER JOIN	sherpa_productToBasket AS pb
        		ON			o.basketId = pb.basketId
        			INNER JOIN	sherpa_products AS p
        			ON			pb.productId = p.id
        	GROUP BY	articleNumber,
        				yearMonth
        	ORDER BY	articleNumber ASC,
        				yearMonth ASC'
        );

        $data = array();
        $products = array();
        $byProduct = array();
        $byMonth = array();

        $i = 0;
        while ($row = $stmt->fetch()) {
        	$data[$i] = $row;
        	$productNames[$row['articleNumber']] = utf8_decode($row['productName'] . ' ' . $row['productLine']);
        	$byProduct[$row['articleNumber']][$row['yearMonth']] = $i;
        	$byMonth[$row['yearMonth']][$row['articleNumber']] = $i;
        	$i++;
        }

        ksort($byMonth);

        // Create Excel file
        $xls = new Spreadsheet_Excel_Writer();

        $titleFormat = $xls->addFormat();
        $titleFormat->setBold();
        $titleFormat->setSize(16);

        $headingFormat = $xls->addFormat();
        $headingFormat->setBold();

        // Worksheet 1: Products grouped by article number
        $line = 0;

        $worksheet = $xls->addWorksheet('Umsatz nach Artikel');
        $worksheet->write(0, 0, 'Umsatz nach Artikel', $titleFormat);
        $line += 1;
        $worksheet->write(1, 0, 'Erstellt ' . date('d.m.Y, H:i'));
        $line += 2;
        $worksheet->write($line, 0, 'MONAT');
        $worksheet->write($line, 1, 'ANZAHL');
        $worksheet->write($line, 2, 'UMSATZ');
        $line += 2;

        foreach ($byProduct as $articleNumber => $yearMonth) {
        	$worksheet->write($line, 0, $articleNumber, $headingFormat);
        	$worksheet->write($line, 1, $productNames[$articleNumber], $headingFormat);
        	$line += 1;
        	$startLine = $line + 1;
        	foreach ($yearMonth as $i) {
        		$worksheet->write($line, 0, $data[$i]['yearMonth']);
        		$worksheet->write($line, 1, $data[$i]['quantity']);
        		$worksheet->write($line, 2, $data[$i]['total']);
        		$line += 1;
        	}
        	$endLine = $line;
        	$worksheet->write($line, 0, 'Total');
        	$worksheet->writeFormula($line, 1, '=SUM(B' . $startLine . ':B' . $endLine . ')');
        	$worksheet->writeFormula($line, 2, '=SUM(C' . $startLine . ':C' . $endLine . ')');
        	$line += 2;
        }

        // Worksheet 2: Products grouped by month
        $line = 0;

        $worksheet = $xls->addWorksheet('Umsatz nach Monat');
        $worksheet->write(0, 0, 'Umsatz nach Monat', $titleFormat);
        $line += 1;
        $worksheet->write(1, 0, 'Erstellt ' . date('d.m.Y, H:i'));
        $line += 2;
        $worksheet->write($line, 0, 'ARTIKEL');
        $worksheet->write($line, 2, 'ANZAHL');
        $worksheet->write($line, 3, 'UMSATZ');
        $line += 2;

        foreach ($byMonth as $yearMonth => $articleNumber) {
        	$worksheet->write($line, 0, $yearMonth, $headingFormat);
        	$line += 1;
        	$startLine = $line + 1;
        	foreach ($articleNumber as $i) {
        		$worksheet->write($line, 0, $data[$i]['articleNumber']);
        		$worksheet->write($line, 1, $productNames[$data[$i]['articleNumber']]);
        		$worksheet->write($line, 2, $data[$i]['quantity']);
        		$worksheet->write($line, 3, $data[$i]['total']);
        		$line += 1;
        	}
        	$endLine = $line;
        	$worksheet->write($line, 0, 'Total');
        	$worksheet->writeFormula($line, 2, '=SUM(C' . $startLine . ':C' . $endLine . ')');
        	$worksheet->writeFormula($line, 3, '=SUM(D' . $startLine . ':D' . $endLine . ')');
        	$line += 2;
        }

        $xls->send('Umsatz pro Artikel ' . date('Y-m-d H:i') . '.xls');
        $xls->close();

        exit;

    }

}